VERSION 5.00
Begin VB.Form frmTest 
   Caption         =   "Test"
   ClientHeight    =   1125
   ClientLeft      =   60
   ClientTop       =   345
   ClientWidth     =   3450
   Icon            =   "frmTest.frx":0000
   LinkTopic       =   "Form1"
   ScaleHeight     =   1125
   ScaleWidth      =   3450
   StartUpPosition =   2  'CenterScreen
   Begin VB.TextBox txtMaxCount 
      Height          =   285
      Left            =   2040
      TabIndex        =   2
      Text            =   "0"
      Top             =   120
      Width           =   1215
   End
   Begin VB.CommandButton cmdTestConnect 
      Caption         =   "cmdDbConnect"
      Enabled         =   0   'False
      Height          =   375
      Left            =   1680
      TabIndex        =   1
      Top             =   720
      Visible         =   0   'False
      Width           =   735
   End
   Begin VB.CommandButton cmdExtract 
      Caption         =   "Extract"
      Height          =   375
      Left            =   2400
      TabIndex        =   0
      Top             =   720
      Width           =   975
   End
   Begin VB.Label lblMaxCount 
      AutoSize        =   -1  'True
      Caption         =   "Limit Requests Per File To"
      Height          =   195
      Left            =   240
      TabIndex        =   3
      Top             =   120
      Width           =   1845
   End
End
Attribute VB_Name = "frmTest"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'   Program to
'1) extract data from sql server database
'2) save data to ascii file



Private Sub cmdExtract_Click()
    Dim objTest As clsExtract
    Dim lngSuccess As Long
    
    Set objTest = New clsExtract
    
    Call InvalidCharacterReplace
    
    lngSuccess = objTest.Extract
    
    MsgBox objTest.GetErrorMessages(lngSuccess), , "Test"
    
    Set objTest = Nothing
    
End Sub

Private Sub cmdTestConnect_Click()

'    Dim blnTest As Boolean
'    Dim rsTest As ADODB.Recordset
'
'''objAdoConn.ConnectString = "Provider=SQLOLEDB.1;Data Source=MSSC05;User ID=sa;Password=;Initial Catalog=pgiz;Persist Security Info=False"
'
'    If objAdoConn Is Nothing Then
'        MsgBox "damn thing;'s not set"
'        Set objAdoConn = New clsAdoConnection
'    End If
'
'    blnTest = objAdoConn.InitConnect
'    If blnTest Then
'        Set rsTest = objAdoConn.OpenRecordset("Select * from dir_funds")
'        If rsTest.RecordCount > 0 Then
'            MsgBox "Yipee!!"
'        Else
'            MsgBox "Uh-oh"
'        End If
'    Else
'        MsgBox "Couldn't connect"
'    End If

End Sub

Private Sub Form_Load()
    frmTest.Height = 1635
    frmTest.Width = 3570
    frmTest.Caption = "Form C Extract"
    lblMaxCount.Top = 160
    lblMaxCount.Left = 120
    txtMaxCount.Top = 120
    txtMaxCount.Left = 2040
    cmdExtract.Top = 720
    cmdExtract.Left = 2280
    cmdExtract.Height = 375
    cmdExtract.Width = 1095
    cmdExtract.Caption = "Extract"
End Sub

Private Sub InvalidCharacterReplace()

'---------------------------------------------------------------------------------------------------------------------
'                      PLEASE REMEMBER TO UPDATE THIS METHOD IN THE OTHER FORM EXTRACTS AS WELL!!!
'---------------------------------------------------------------------------------------------------------------------

    Dim ConnectDLL As Object
    Dim Connection As ADODB.Connection
    Dim ConnectionString As String
    Dim SQLString As String
    Dim RecordsAffected As Long
    Dim sProgressText As String
    
On Error GoTo PROC_ERROR
    Set ConnectDLL = CreateObject("Connect.clsConnect")
    Set Connection = New ADODB.Connection
    
    ConnectionString = ConnectDLL.ConnectionString
    
    With Connection
        .CursorLocation = adUseClient
        .ConnectionString = ConnectionString
        .Open
    End With
    
    sProgressText = ""
    
    SQLString = "update DIR_REQUEST set"
    SQLString = SQLString & " SURNAME = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(SURNAME, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " FIRSTNAMES = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(FIRSTNAMES, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " INITIALS = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(INITIALS, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " HOME_ADDR_L1 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(HOME_ADDR_L1, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " HOME_ADDR_L2 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(HOME_ADDR_L2, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " HOME_ADDR_L3 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(HOME_ADDR_L3, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " HOME_ADDR_L4 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(HOME_ADDR_L4, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')" & vbCrLf
    SQLString = SQLString & " where SCOD_PROCESS_STATUS in ('CA', 'EX');"
    
    Connection.Execute SQLString, RecordsAffected
    
    sProgressText = sProgressText & RecordsAffected & " record(s) updated on DIR_REQUEST" & vbCrLf
    
    
    
    SQLString = "update DIR_CONTACT_DETAILS set"
    SQLString = SQLString & " CONTACT_NAME = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(CONTACT_NAME, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L1 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L1, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L2 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L2, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L3 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L3, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L4 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L4, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')" & vbCrLf
    SQLString = SQLString & " WHERE FUN_ID in (select fun_id from dir_request where SCOD_PROCESS_STATUS in ('CA', 'EX'));"
    
    Connection.Execute SQLString, RecordsAffected
    
    sProgressText = sProgressText & RecordsAffected & " record(s) updated on DIR_CONTACT_DETAILS" & vbCrLf
    
    
    
    SQLString = "update DIR_CONTACT_DETAILS set"
    SQLString = SQLString & " CONTACT_NAME = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(CONTACT_NAME, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L1 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L1, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L2 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L2, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L3 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L3, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L4 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L4, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '<', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')" & vbCrLf
    SQLString = SQLString & " WHERE EMP_ID in (select EMP_ID from dir_request where SCOD_PROCESS_STATUS in ('CA', 'EX'));"
    
    Connection.Execute SQLString, RecordsAffected
    
    sProgressText = sProgressText & RecordsAffected & " record(s) updated on DIR_CONTACT_DETAILS" & vbCrLf
    
    
    
    SQLString = "update DIR_REQUEST_FORMS set"
    SQLString = SQLString & " POST_ADDR_L1 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L1, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L2 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L2, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L3 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L3, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')," & vbCrLf
    SQLString = SQLString & " POST_ADDR_L4 = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(POST_ADDR_L4, '.', ' '), ',', ' '), '(', ' '), ')', ' '), ':', ' '), ';', ' '), '%', ' '), '#', ' '), '&', ' '), '>', ' '), '[', ' '), ']', ' '), '@', ' '), '"" ', ' '), '`', ' '), '+', ' '), '=', ' '), '_', ' ')" & vbCrLf
    SQLString = SQLString & " WHERE REQ_SEQ_NUM in (select REQ_SEQ_NUM from dir_request where SCOD_PROCESS_STATUS in ('CA', 'EX'));"
    
    Connection.Execute SQLString, RecordsAffected
    
    sProgressText = sProgressText & RecordsAffected & " record(s) updated on DIR_REQUEST_FORMS" & vbCrLf
    
    MsgBox sProgressText, vbOKOnly, "Invalid Character Replace"
    
    sProgressText = ""
    
PROC_EXIT:
    Connection.Close
    
    Set Connection = Nothing
    Set ConnectDLL = Nothing
    
    Exit Sub
    
PROC_ERROR:
    MsgBox "An error occurred. Error:" & Err.Description
    
End Sub


